package com.haoxi.gateway.common;

import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class SQLConst {

    public final static DateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
    public final static String SELECT_BUSINESSREGISTER_SQL = "select * from tb_business_register where customer_cellphone_number = ?  and business_id= ?";
    
    public final static String SAVE_MSG_SUBMIT_SQL = "INSERT INTO SUBMITTEMP(GATEWAYCODE,STYPE,fee_code,ORGMOBILE,ORGMOBILETYPE,DESTMOBILE,DESTMOBILETYPE,FEEMOBILE,FEEMOBILETYPE,FEE_USERTYPE,FEE_TYPE,FEEMONEY,MSGFMT,CONTENT,RESERVED,MTFLAG,REPORTFLAG,TPPID,TPUDHID,CP,TID,PROVINCE,PROVINCENAME,CITYNUMBER,CITYNAME,ISDEAL,REG_DEL,FEE_RECEIPT,SAVETIME) VALUES ('999999',0,'TGD6919901','106573450968',0,?,0,?,0,'0','01',0,0,?,'reserved',0,0,0,0,0,'0',0,'0',0,'0',0,0,NULL,?)";
    
    public final static String SELECT_EMPLOYEE_SQL = "select * from tb_external_employee where is_active='1' and saleman_cellphone_number = ?";
    
    public final static String SELECT_BUSINESS_SQL = "select * from tb_business where is_active='1' and sms_code = ?";
    
    public final static String INSERT_BUSINESSREGISTER_SQL = new StringBuffer(
    "insert into tb_business_register ")
    .append("(business_id, customer_cellphone_number , is_checked, registered_date, saleman_name, saleman_cellphone_number, check_type, branch_level0_code,  branch_level0_name, branch_level1_code, branch_level1_name, branch_level2_code, branch_level2_name, branch_level2_ownership, branch_level2_ownership_cellphone_number, branch_level2_ownership_id,branch_lever2_star_level) ")
    .append("values ")
    .append("(?                  ,            ?                                      ,     ?              ,           GETDATE()        ,              ?           ,                          ?                      ,          ?         ,                ?                 ,                   ?                  ,                    ?             ,                   ?                ,                  ?               ,                      ?             ,            ?                               ,                       ?                                                      ,                           ?,?)")
    .toString();

    // ------------Oracle-----------sql--------------------------------------------------------------------------------
    public final static String INSERT_USERMO_SQL = new StringBuffer(
            "insert into deliver ")
            .append("(gatewaycode, modulesroute, msgid, destmobile, orgmobile, content, reg_del, msg_fmt, stat,  savetime, reserved, cp, orgmobiletype, province, citynumber, cityname, mobiletype) ")
            .append("values ")
            .append("(?          ,            0, ?      , ?             , ?,        ?,      -1,     ?,      '',    GETDATE(), ?,          0,      ?,      0,          0,          '',         '')")
            .toString();

//  public final static String INSERT_USERMO_SQL = new StringBuffer(
//  "insert into sms.deliver ")
//  .append(
//          "(id, gatewaycode, modulesroute, msgid, destmobile, orgmobile, content, reg_del, msg_fmt, stat, submittime, donetime, savetime, reserved, cp, orgmobiletype, province, citynumber, cityname, mobiletype) ")
//  .append("values ")
//  .append(
//          "(sms_deliver_id_seq.nextval,  ?,  0, ?, ?, ?, ?, -1, ?, '', '', '', sysdate, ?, 0, ?, 0, 0, '', '')")
//  .toString();
    public final static String SAVE_HISTORY_ERROR_MSG = new StringBuffer(
            "INSERT INTO history.error_msg NOLOGGING ")
            .append(
                    "VALUES(history.history_msghistory_id_seq.nextval,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?,?,?,?,?,?)")
            .toString();

//    public final static String SAVE_MSG_HISTORY = new StringBuffer(
//            "INSERT INTO MSGHISTORY_MT(id, msgid, stype, service_id, gatewaycode, orgmobile, orgmobiletype,  destmobile, destmobiletype, feemobile,  feemobiletype, feetype, feecode, content, submitstatus, province,  citynumber, cityname, reserved,  cp, op, spsequence, moormt, substype, submittime, savetime,MOMT_ID) ")
//            .append(
//                    "VALUES(history.history_msghistory_id_seq.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,0,to_date(?,'yyyy-mm-dd HH24:MI:SS'),sysdate,?)")
//            .toString();
    public final static String SAVE_MSG_HISTORY = new StringBuffer(
    "INSERT INTO MSGHISTORY_MT(msgid, stype, service_id, gatewaycode, orgmobile, orgmobiletype,  destmobile, destmobiletype, feemobile,  feemobiletype, feetype, feecode, content, submitstatus, province,  citynumber, cityname, reserved,  cp, op, spsequence, moormt, substype, submittime, savetime,MOMT_ID) ")
    .append(
            "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,0,?,GETDATE(),?)")
    .toString();

    public final static String SAVE_MSG_HISTORY_STATUS = new StringBuffer(
            "INSERT INTO history.msgstatus(id, msgid, stype, service_id, gatewaycode, orgmobile, orgmobiletype,  destmobile, destmobiletype, feemobile,  feemobiletype, feetype, feecode, content, submitstatus, province,  citynumber, cityname, reserved,  cp, op, spsequence, moormt, substype, submittime, savetime, status, MOMT_ID) ")
            .append(
                    "VALUES(history.HISTORY_MSGSTATUS_ID_SEQ.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,0,to_date(?,'yyyy-mm-dd HH24:MI:SS.***'),sysdate,?,?)")
            .toString();

    /**
     * save sky3rd report
     */
    public final static String SAVE_SKY3RD_STATUS = new StringBuffer(
            "insert into sms.partner_msg_status(id,cp,msgid,status,sid,gateway,savetime)")
            .append(
                    " values(sms.SMS_PARTNER_STATUS_ID_SEQ.nextval,?,?,?,?,?,sysdate)")
            .toString();
    // ---------------subdsmp-----------------------------------------------------------------------------
    public final static String UPDATE_DEALED_SUBSCRIBEREQ = "update sms.subscribereq set isdeal= 0 where id = ?";

    // public final static String SAVE_SUBSCRIBEREQ = new StringBuffer(
    // "insert into sms.subscriberesp
    // (id,msgtype,transactionid,hret,linkid,isdeal,gatewaycode,savetime) ")
    // .append("values ")
    // .append(
    // "(sms.sms_subscriberesp_id_seq.nextval,?,?,?,?,-1,?,sysdate)")
    // .toString();
    public final static String SAVE_SUBSCRIBEREQ = "insert into sms.subscriberesp (id,msgtype,transactionid,hret,linkid,isdeal,gatewaycode,savetime) values (sms.sms_subscriberesp_id_seq.nextval,?,?,?,?,-1,?,sysdate)";
    public final static String DEAL_SUBSCRIBEREQ = "{call app.PROC_APP_CANCAL_OPERATION(?,?,?,?)}";

    public final static String SELECT_NEED_DEAL_SUBSCRIBEREQ = "select * from sms.subscribereq where isdeal=-1 and gatewaycode = ? and rownum<=100 order by savetime asc";
    
    public final static String SELECT_NEED_DEAL_SUBSCRIBEREQ_LT = "select * from sms.subscribereq_lt where status=-1 and gatewaycode = ? and rownum<=100 order by savetime asc";
    
    public final static String UPDATE_NEED_DEAL_SUBSCRIBEREQ_LT = "update sms.subscribereq_lt set status=0, hret=?,sendtime=sysdate where id=?";

    /**
     * delete dealed MT
     */
    public final static String DELETE_DEALED_MT_BY_ID = "delete from submittemp where id = ?";

    /**
     * select MT by id
     */
    public final static String GET_SUBMIT_BY_ID = "SELECT * FROM submit WHERE id = ?";

    /**
     * GET ALL NEED SEND MT in oracle9i
     */
    public final static String GET_NEED_SEND_MT = new StringBuffer(
            "select * from").append("  (select * from smsp.view_submit")
            .append("    where isdeal=-1").append("      and reportFlag<=9")
            .append("      and gatewaycode=?").append(
                    "      and savetime<=sysdate").append(
                    "    order by reportFlag,savetime)").append(
                    "  where rownum<=?").toString();

    /**
     * GET ALL NEED SYN SPECIAL MOBILE in oracle9i
     */
    public final static String SYN_SEL_SENESEMOBILE = "select id, mobile, detail from sms.sensemobile where status = -1 and gatewaycode =? and rownum <=200";
    public final static String SYN_SEL_BLACKMOBILE = "select id, mobile, remark from sms.black where status = -1 and gatewaycode =? and rownum <=200";
    public final static String SYN_SEL_BLACKLONGNUM = "select id, longnumber, remark from smsp.smsp_black_longnumber where status = -1 and gatewaycode =? and rownum <=200";
    public final static String SYN_UPD_BLACKMOBILE = "update sms.black set status=0 where id=?";
    public final static String SYN_UPD_SENSEMOBILE = "update sms.sensemobile set status=0 where id=?";
    public final static String SYN_UPD_BLACKLONGNUM = "update smsp.smsp_black_longnumber set status=0 where id=?";
    
    // ---------------Oracle----------end-----------------------------------------------------------------------------
    // ---------------MYSQL----------start----------------------------------------------------------------------------
    /**
     * save synData
     */
    public final static String SYN_SAVE_SENESEMOBILE = "insert into blacklongnum(ID,longnumber,REMARK,SAVETIME) values(?,?,?,GETDATE())";
    public final static String SYN_SAVE_BLACKMOBILE = "insert into blackmobile(ID,MOBILE,REMARK,SAVETIME) values(?,?,?,GETDATE())";
    public final static String SYN_SAVE_BLACKLONGNUM = "insert into sensemobile(ID,MOBILE,DETAIL,SAVETIME) values(?,?,?,GETDATE())";
    
    /**
     * select sensemobile
     */
    public final static String SELECT_SENESEMOBILE = "select MOBILE,DETAIL from sensemobile";

    /**
     * select blackmobile
     */
    // public final static String SELECT_BLACKMOBILE = "select mobile,remark
    // from sms.black";
    public final static String SELECT_BLACKMOBILE = "select MOBILE,REMARK from blackmobile";

    /**
     * select black longnum
     */
    public final static String SELECT_BLACKLONGNUM = "select longnumber, REMARK from blacklongnum";

    public final static String SYN_INS_BLACKMOBILE = "insert into blackmobile(id,mobile,remark,savetime) values(?,?,?,?)";

    /**
     * GET ALL NEED SEND MT in mysql 4.0
     */
    public final static String GET_NEED_SEND_MT_MySQL = new StringBuffer(
            "select * from submittemp order by SAVETIME asc").toString();

    /**
     * save the needed status and sended MT
     */
    public final static String SAVE_NEED_SUBMITSTATUS_MTDATA = new StringBuffer(
            "INSERT INTO sendedmttemp(SEQUENCEID,STYPE,SERVICE_ID,ORGMOBILE,ORGMOBILETYPE,DEDESTMOBILE,DESTMOBILETYPE,FEEMOBILE,FEEMOBILETYPE,FEETYPE,FEECODE,CONTENT,PROVINCE,CITYNUMBER,CITYNAME,RESERVED,CP,SUBMITTIME,GATEWAYCODE,SID,MOMT_ID) ")
            .append("VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
            .toString();

    /**
     * delete the need status and sended SubmitCMD
     */
    public final static String DELETE_NEED_SUBMITSTATUS_MTDATA = "Delete from sendedmttemp where SEQUENCEID=?";

    /**
     * save the need report and sended SubmitCMD
     */
    public final static String SAVE_NEED_REPORT_MTDATA = new StringBuffer(
            "INSERT INTO needreportmttemp(MSGID,SUBMITSTATUS,SEQUENCEID,STYPE,SERVICE_ID,ORGMOBILE,ORGMOBILETYPE,DEDESTMOBILE,DESTMOBILETYPE,FEEMOBILE,FEEMOBILETYPE,FEETYPE,FEECODE,CONTENT,PROVINCE,CITYNUMBER,CITYNAME,RESERVED,CP,SUBMITTIME,GATEWAYCODE,SAVETIME,SID,MOMT_ID) ")
            .append(
                    "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,GETDATE(),?,?)")
            .toString();

    /**
     * delete the need report and success sended SubmitCMD
     */
    public final static String DELETE_NEED_REPORT_MTDATA = "Delete from needreportmttemp where MSGID=?";

    /**
     * save the recived report
     */
    public final static String SAVE_MT_REPORT_DATA = new StringBuffer(
            "INSERT INTO mtreporttemp(MSGID,MOBILE,MOBILETYPE,status,SAVETIME) ")
            .append("VALUES(?,?,?,?,GETDATE())").toString();

    /**
     * save the recived report
     */
    public final static String DELETE_MT_REPORT_DATA = "Delete from mtreporttemp where MSGID=?";

    /**
     * select的SENDED_MT
     */
    public final static String SELECT_SENDED_MT = "SELECT * FROM sendedmttemp ORDER BY SUBMITTIME ASC";

    /**
     * 删除超时的SENDED_MT
     */
    public final static String DELETE_OVERDATE_SENDED_MT = "Delete from sendedmttemp";

    /**
     * 匹配需要处理的report
     */
    public final static String SELECT_NEED_DEAL_REPORT = new StringBuffer(
            "SELECT t2.MSGID id,SUBMITSTATUS,SEQUENCEID,STYPE,SERVICE_ID,ORGMOBILE,ORGMOBILETYPE,DEDESTMOBILE,DESTMOBILETYPE,FEEMOBILE,FEEMOBILETYPE,FEETYPE,FEECODE,CONTENT,PROVINCE,CITYNUMBER,CITYNAME,RESERVED,CP,GATEWAYCODE,SUBMITTIME,STATUS, t2.SAVETIME doneTime,SID,MOMT_ID FROM needreportmttemp t1, mtreporttemp t2 ")
            .append("WHERE t1.MSGID = t2.MSGID ").append(
                    " ORDER BY t2.SAVETIME ASC LIMIT ").toString();

    /**
     * 查询过时的数据
     */
    public final static String SELECT_OVERDATE_MTREPORT_DATA = "select * from mtreporttemp t where (GETDATE() - INTERVAL "
            + SYSConst.OVERDATE_NEEDREPORT_DATA_TIME + " HOUR) > t.saveTime";
    public final static String SELECT_OVERDATE_NEEDREPORT_MTDATA = "select * from needreportmttemp t where (GETDATE() - INTERVAL "
            + SYSConst.OVERDATE_NEEDREPORT_DATA_TIME + " HOUR) > t.saveTime";
    public final static String DELETE_OVERDATE_MTREPORT_DATA = "delete from mtreporttemp t where t.MSGID = ? ";
    public final static String DELETE_OVERDATE_NEEDREPORT_MTDATA = "delete from needreportmttemp t where t.MSGID = ? ";
    public final static String SAVE_OVERDATE_MTREPORT_DATA = new StringBuffer(
            "INSERT INTO overdatemtreport(MSGID,MOBILE,MOBILETYPE,status,SAVETIME) ")
            .append("VALUES(?,?,?,?,?)").toString();

    // ---------------MYSQL----------end-----------------------------------------------------------------------------
}
